<?php 

require_once dirname(__FILE__).'/OController.php';

class ReportController extends OController
{
	public $menu = array(
		array('label' => 'Valid Members Report', 'url' => array('owner/report/validMembers')),
		array('label' => 'First Order Report', 'url' => array('owner/report/firstOrder')),
		//array('label' => 'Commission Report', 'url' => array('owner/report/commission')),
	);
    
    public function actionValidMembers() {
    	$website_id = !empty($_GET['website_id'])? $_GET['website_id'] : '';
    	$year_start = !empty($_GET['year_start'])? $_GET['year_start'] : date('Y');
    	$year_end = !empty($_GET['year_end'])? $_GET['year_end'] : date('Y');
    	$month_start = !empty($_GET['month_start'])? $_GET['month_start'] : date('n');
    	$month_end = !empty($_GET['month_end'])? $_GET['month_end'] : date('n');
    	$day_start = !empty($_GET['day_start'])? $_GET['day_start'] : 1;
    	$day_end = !empty($_GET['day_end'])? $_GET['day_end'] : date('d');
    	$style = !empty($_GET['style'])? $_GET['style'] : 'detail';
    	
    	$date_start = date("Y-m-d", strtotime("{$year_start}-{$month_start}-{$day_start}"));
    	$date_end = date("Y-m-d", strtotime("{$year_end}-{$month_end}-{$day_end}"));
    	
    	$data = array(
    		'website_id' => $website_id,
    		'year_start' => $year_start,
    		'year_end' => $year_end,
    		'month_start' => $month_start,
    		'month_end' => $month_end,
    		'day_start' => $day_start,
    		'day_end' => $day_end,
    		'style' => $style,
    		'block_title' => 'Valid Members Report'
    	);
    	if ($style == 'detail') {
    		$data['dataProvider'] = $this->validMembersDetail($website_id, $date_start, $date_end);
    	} else {
    		$data['dataProvider'] = $this->getSummary($website_id, $date_start, $date_end);
    	}
    	
    	$this->render('validMembers', $data);
    }

    
    public function actionFirstOrder() {
    	$website_id = !empty($_GET['website_id'])? $_GET['website_id'] : '';
    	$year_start = !empty($_GET['year_start'])? $_GET['year_start'] : date('Y');
    	$year_end = !empty($_GET['year_end'])? $_GET['year_end'] : date('Y');
    	$month_start = !empty($_GET['month_start'])? $_GET['month_start'] : date('n');
    	$month_end = !empty($_GET['month_end'])? $_GET['month_end'] : date('n');
    	$day_start = !empty($_GET['day_start'])? $_GET['day_start'] : 1;
    	$day_end = !empty($_GET['day_end'])? $_GET['day_end'] : date('d');
    	$style = !empty($_GET['style'])? $_GET['style'] : 'detail';
    	
    	$date_start = date("Y-m-d", strtotime("{$year_start}-{$month_start}-{$day_start}"));
    	$date_end = date("Y-m-d", strtotime("{$year_end}-{$month_end}-{$day_end}"));
    	
    	$data = array(
    		'website_id' => $website_id,
    		'year_start' => $year_start,
    		'year_end' => $year_end,
    		'month_start' => $month_start,
    		'month_end' => $month_end,
    		'day_start' => $day_start,
    		'day_end' => $day_end,
    		'style' => $style,
    		'block_title' => 'First Order Report'
    	);
    	if ($style == 'detail') {
    		$data['dataProvider'] = $this->firstOrderDetail($website_id, $date_start, $date_end);
    	} else {
    		$data['dataProvider'] = $this->getSummary($website_id, $date_start, $date_end);
    	}
    	
    	$this->render('firstOrder', $data);
    }
    
    protected function validMembersDetail($website_id, $date_start, $date_end) {
    	$cond = "owner_id='".Yii::app()->user->id."' 
    			AND date(date_join) >= '$date_start' 
    			AND date(date_join) <= '$date_end' 
    			AND accept='Y'";
    	if (!empty($website_id)) {
    		$cond .= " AND website_id=$website_id"; 
    	}
    	$arr = array(
    		'pagination' => array('pageSize' => 30),
    		'criteria' => array(
    			'condition' => $cond,
    			'order' => 'id DESC'
    		) 
    	);
    	
    	return new CActiveDataProvider('User', $arr);
    }
    
	protected function getSummary($website_id, $date_start, $date_end) {
    	$arr = array('pagination' => array('pageSize' => 30));
    	$cond = "1";
    	if (!empty($website_id)) {
    		$cond .= " AND website_id=$website_id";
    	}
    	$cond .= " AND date(adddate) >= '$date_start' AND date(adddate) <= '$date_end'";
    	$sql = "SELECT 
    				website Website,
    				sum(check_firstordernum) Orders,
    				sum(validclick) Clicks,
    				sum(vjoinmb) Members
    			FROM diary_log
    			WHERE $cond
    			GROUP BY website_id
    			UNION
    			SELECT 
    				'Total' Website,
    				sum(check_firstordernum) Orders,
    				sum(validclick) Clicks,
    				sum(vjoinmb) Members
    			FROM diary_log
    			WHERE $cond";
    	$arr['totalItemCount'] = 4;
    	
    	return new QpidSqlDataProvider($sql, $arr);
    }
    
    protected function firstOrderDetail($website_id, $date_start, $date_end) {
    	$cond = "owner_id='".Yii::app()->user->id."'
    			AND verify_status=1 
    			AND adddate >= '$date_start' 
    			AND adddate <= '$date_end'";
    	if (!empty($website_id)) {
    		$cond .= " AND website_id=$website_id";
    	}
    	
    	$sql = "SELECT f.payment_status, h.*
    			FROM first_order f, order_history h
    			WHERE f.order_id=h.order_id
    			AND $cond
    			ORDER BY order_history_id DESC";
    	$coun_sql = "SELECT count(*)
    			FROM first_order f, order_history h
    			WHERE f.order_id=h.order_id
    			AND $cond";
    	$count = Yii::app()->db->createCommand($coun_sql)->queryScalar();
    	$arr = array(
    		'pagination' => array('pageSize' => 30),
    		'totalItemCount' => $count,
    	);
    	return new QpidSqlDataProvider($sql, $arr);
    }
}